<?php
namespace app\channel\controller;

use app\common\model\Order as GamePayLogModel;
use app\common\model\Stat as Stats;
use PHPExcel;
use PHPExcel_IOFactory;
use think\Controller;
use think\Db;
use think\facade\Session;

class ExportExcel extends Controller
{
    public function exportExcel(){
        $data = $_REQUEST;
        $type = $data["type"];
        ini_set('memory_limit', '512M');
        set_time_limit(0);
        switch ($type){
            case 'user':
                $result = self::export_user($data);
                $expTitle = $result['title'];
                $expCellName = $result['cells'];
                $expTableData = $result['tableData'];
                break;
            case 'order':
                $result = self::export_order($data);
                $expTitle = $result['title'];
                $expCellName = $result['cells'];
                $expTableData = $result['tableData'];
                break;
            case 'stat':
                $result = self::export_stat($data);
                $expTitle = $result['title'];
                $expCellName = $result['cells'];
                $expTableData = $result['tableData'];
                break;
            case 'days':
                $result = self::export_days($data);
                $expTitle = $result['title'];
                $expCellName = $result['cells'];
                $expTableData = $result['tableData'];
                break;
            default:
                $this->error("导出类型错误！");
        }
        self::export($expTitle,$expCellName,$expTableData);
    }

    private static function export_order($data)
    {
        $map[]  = ['cid' ,'=',Session::get('channel_id')];
        if(!empty($data['keyword'])){
            $map[]  = ['a.uid|a.order_no','like', '%'.$data['keyword'].'%'];
        }
        if(!empty($data['game'])){
            $map[]   = ['a.gid','=' ,$data['game']];
        }
        if(!empty($data['area'])){
            $map[]  = ['a.sid','=',$data['area']];
        }

        $map[]  = ['a.order_status','gt',0];

        if (!empty($data['starttime']) && !empty($data['endtime'])) {
            $map[]= ['a.pay_time','between time',[$data['starttime'],$data['endtime']]];
        }elseif (!empty($data['starttime'])) {
            $map[] = ['a.pay_time','>=',$data['starttime']];
        }elseif (!empty($data['endtime'])) {
            $map[] = ['a.pay_time','<=',$data['endtime']];
        }

        $field = 'a.*,b.name,c.sname,d.username as aname,d.nickname';
        $pay_list = GamePayLogModel::alias('a')
            ->join('Gamelist b','b.id = a.gid','left')
            ->join('Gamearea c','c.sid = a.sid','left')
            ->join('Channel d','d.id = a.cid','left')
            ->where($map)->field($field)
            ->order('a.id DESC')
            ->select();

        $expTitle = "订单列表";
        $expCellName = array(
            array('order_no','订单号'),
            array('account','玩家帐号'),
            array('game_name','游戏名称'),
            array('amount','充值金额'),
            array('pay_time','充值时间'),
            array('order_status','订单状态'),
        );

        $pay_status = [
            '0' => '未支付',
            '1' => '已支付',
            '2' => '已充值',
            '3' => '已发货',
            '4' => '通知游戏失败',
        ];
        $expTableData = [];
        foreach ($pay_list as $val){
            $expTableData[] = array(
                'order_no' => $val['order_no'],
                'account' => get_player($val['uid'],'account'),
                'game_name' => $val['name'],
                'amount' => $val['amount'],
                'pay_time' => $val['pay_time'],
                'order_status' => $pay_status[$val['order_status']],
            );
        }
        return ['title'=>$expTitle,'cells'=>$expCellName,'tableData'=>$expTableData];
    }

    public static function export_stat($data){
        $expTitle = "统计列表";
        $expCellName = array(
            array('gid','游戏名称'),
            array('stat_date','日期'),
            array('new_users','新增用户'),
            array('new_roles','新增角色'),
            array('active_users','活跃人数'),
            array('pay_amount','付费金额'),
            array('pay_users','付费人数'),
            array('pay_times','付费次数'),
            array('arpu','ARPU'),
            array('arppu','ARPPU'),
        );
        $stat = new Stats();
        $where[] = ['cid', '=', Session::get('channel_id')];
        if(!empty($data['gid'])){
            $where[] = ['gid', '=', $data['gid']];
        }
        if (!empty($data['starttime']) && !empty($data['endtime'])) {
            $where[]= ['stat_date','between time',[$data['starttime'],$data['endtime']]];
        }elseif (!empty($data['starttime'])) {
            $where[] = ['stat_date','>=',$data['starttime']];
        }elseif (!empty($data['endtime'])) {
            $where[] = ['stat_date','<=',$data['endtime']];
        }
        $list = $stat->where($where)
            ->order("id desc")
            ->select()->toArray();
        $expTableData = [];
        foreach ($list as $val){
            $expTableData[] = array(
                'gid' => get_game_filed($val['gid'],'name'),
                'stat_date' => $val['stat_date'],
                'new_users' => $val['new_users'],
                'new_roles' => $val['new_roles'],
                'active_users' => $val['active_users'],
                'pay_amount' => $val['pay_amount'],
                'pay_users' => $val['pay_users'],
                'pay_times' => $val['pay_times'],
                'arpu' => $val['arpu'],
                'arppu' => $val['arppu'],
            );
        }
        return ['title'=>$expTitle,'cells'=>$expCellName,'tableData'=>$expTableData];
    }

    public static function export_days($data)
    {
        $expTitle = "统计列表";
        $expCellName = array(
            array('stat_date','日期'),
            array('new_users','新增用户'),
            array('new_roles','新增角色'),
            array('active_users','活跃人数'),
            array('pay_amount','付费金额'),
            array('pay_users','付费人数'),
            array('pay_times','付费次数'),
            array('arpu','ARPU'),
            array('arppu','ARPPU'),
        );
        $stat = new Stats();
        $where[] = ['cid', '=', Session::get('channel_id')];

        if (!empty($data['starttime']) && !empty($data['endtime'])) {
            $where[]= ['stat_date','between time',[$data['starttime'],$data['endtime']]];
        }elseif (!empty($data['starttime'])) {
            $where[] = ['stat_date','>=',$data['starttime']];
        }elseif (!empty($data['endtime'])) {
            $where[] = ['stat_date','<=',$data['endtime']];
        }
        $list = $stat->field('stat_date, sum(new_users) as new_users, sum(new_roles) as new_roles,
            sum(active_users) as active_users, sum(pay_amount) as pay_amount, sum(pay_users) as pay_users, sum(pay_times) as pay_times')
            ->where($where)
            ->order("id desc")
            ->group('stat_date')
            ->select()->toArray();
        $expTableData = [];
        foreach ($list as $val){
            $activeUsers = $val['active_users'] ?? 0;
            $payAmount = $val['pay_amount'] ?? 0;
            $payUsers = $val['pay_users'] ?? 0;

            $arpu = $activeUsers > 0 ? round($payAmount / $activeUsers, 2) : 0;
            $arppu = $payUsers > 0 ? round($payAmount / $payUsers, 2) : 0;

            $expTableData[] = array(
                'stat_date' => $val['stat_date'],
                'new_users' => $val['new_users'],
                'new_roles' => $val['new_roles'],
                'active_users' => $val['active_users'],
                'pay_amount' => $val['pay_amount'],
                'pay_users' => $val['pay_users'],
                'pay_times' => $val['pay_times'],
                'arpu' => $arpu,
                'arppu' => $arppu,
            );
        }
        return ['title'=>$expTitle,'cells'=>$expCellName,'tableData'=>$expTableData];
    }
    private static function export_user($data){
        $age_status = [
            '0' => '未认证',
            '1' => '未成年',
            '2' => '已成年',
        ];
        $expTitle = "玩家列表";
        $expCellName = array(
            array('id','玩家ID'),
            array('account','玩家帐号'),
            array('game_name','注册游戏'),
            array('channel','所属渠道'),
            array('login_time','最后登录时间'),
            array('login_ip','登录IP'),
            array('status','账号状态'),
            array('type','注册方式'),
            array('is_age','是否实名认证'),
        );
        $map = [];
        if(!empty($data['ip'])){
            $map[]  = ['login_ip','=',$data['ip']];
        }
        if(!empty($data['game'])){
            $map[]   = ['gid','=' ,$data['game']];
        }
        if (!empty($data['starttime']) && !empty($data['endtime'])) {
            $map[]= ['between time',[strtotime($data['starttime']),strtotime($data['endtime'])]];
        }elseif (!empty($data['starttime'])) {
            $map[] = ['login_time','>=',strtotime($data['starttime'])];
        }elseif (!empty($data['endtime'])) {
            $map[] = ['login_time','<=',strtotime($data['endtime'])];
        }
        $expTableData = \app\common\model\Player::field('id,account,gid,cid,login_time,login_ip,status,type,is_age')->where($map)->order("id desc")->select()->toArray();
        foreach ($expTableData as &$key){
            $key['account'] = "'".$key['account'];
            $key['game_name'] = get_game_filed($key['gid'],'name');
            $key['channel'] = get_channel_name($key['cid'],'nickname');
            $key['login_time'] = date("Y-m-d H:i:s",$key['login_time']);
            $key['status'] = $key['status'] == 1 ? '正常' : '禁用';
            $key['type'] = $key['type'] == 1 ? '手机号注册' : '账号注册';
            $key['is_age'] = $age_status[$key['is_age']];
            unset($key['gid']);
            unset($key['cid']);
        }
        return ['title'=>$expTitle,'cells'=>$expCellName,'tableData'=>$expTableData];
    }

    private static function export($expTitle,$expCellName,$expTableData){
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $fileName = session('user_auth.username').date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);
        $objPHPExcel = new PHPExcel();
        $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        for($i=0;$i<$cellNum;$i++){
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $expCellName[$i][1]);
        }
        //数据处理
        foreach ($expTableData as $key => $val) {
            foreach ($expCellName as $k => $v) {
                if (isset($v[2])) {
                    $ar_k = array_search('*', $v);
                    if ($ar_k !== false) {
                        $v[$ar_k] = $val[$v[0]];
                    }
                    $fun = $v[2];
                    $param = $v;
                    unset($param[0], $param[1], $param[2]);
                    $val[$v[0]] = call_user_func_array($fun, $param);
                }
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$k].($key+3), $val[$v[0]]);
            }
        }
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xlsx"');
        header("Content-Disposition:attachment;filename=$xlsTitle.xls");//attachment新窗口打印inline本窗口打印
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }
}
